<?php

use \dayscript\acl\persistence\AclPersistence;
use \dayscript\acl\Acl;
use \dayscript\datasource\Database;

/**
 * MySQLPersistence
 *
 * Persistence ACL in MySQL database engine
 *
 * @subpackage
 * @author		Noel Marin <nmarin@dayscript.com>
 * @copyright	2009 Dayscript Ltda.
 * @license
 * @version		1.0
 * @version		$Revision: 0 $
 * @filesource
 * @link		http://www.dayscript.com
 * @link		{docsLink}
 * @since		1.0
 * @modifiedby	$LastChangedBy: Noel Marin $
 * @modified	$Date: 2009-09-24 $
 */
class MySQLPersistence extends \dayscript\acl\persistence\AclPersistence {

	/**
	 * Constructor
	 *
	 * @param $acl ACL
	 * @param $dataSource Database datasource
	 * @return void
	 */
	public function MySQLPersistence(\dayscript\datasource\sql\Database $mysql){
		$this->acl = null;
		$this->dataSource = $mysql;
	}

	/**
	 * @see \dayscript\acl\persistence\AclPersistence
	 */
	public function init(){
		if(!$this->dataSource->isConnected()){
			$this->dataSource->getConnection();
		}

		try{
			// Default ACL structure
			$sql = "CREATE TABLE IF NOT EXISTS acl_role (" .
				"id_acl_role VARCHAR(100) NOT NULL, " .
				"PRIMARY KEY (id_acl_role) " .
				") ENGINE=InnoDB ; ";
			$this->dataSource->query($sql);

			$sql = "CREATE TABLE IF NOT EXISTS acl_role_parents (" .
				"id_acl_role VARCHAR(100) NOT NULL, " .
				"parent_id_acl_role VARCHAR(100) NOT NULL, " .
				"PRIMARY KEY (id_acl_role, parent_id_acl_role) " .
				") ENGINE=InnoDB ; ";
			$this->dataSource->query($sql);

			$sql = "CREATE TABLE IF NOT EXISTS acl_resource (" .
				"id_acl_resource VARCHAR(100) NOT NULL, " .
				"parent_id_acl_resource VARCHAR(100), " .
				"PRIMARY KEY (id_acl_resource) " .
				") ENGINE=InnoDB ; ";
			$this->dataSource->query($sql);

			$sql = "CREATE TABLE IF NOT EXISTS acl_privilegies (" .
				"id_acl_role VARCHAR(100) NOT NULL, " .
				"id_acl_resource VARCHAR(100) NOT NULL, " .
				"privilegie VARCHAR(200) NOT NULL, " .
				"PRIMARY KEY (id_acl_role, id_acl_resource, privilegie) " .
				") ENGINE=InnoDB ; ";
			$this->dataSource->query($sql);

			$sql = "ALTER TABLE acl_role_parents " .
				"ADD CONSTRAINT fk_acl_role1 FOREIGN KEY (id_acl_role) REFERENCES acl_role (id_acl_role), " .
				"ADD CONSTRAINT fk_acl_role2 FOREIGN KEY (parent_id_acl_role) REFERENCES acl_role (id_acl_role); ";
			$this->dataSource->query($sql);

			$sql = "ALTER TABLE acl_resource " .
				"ADD CONSTRAINT fk_acl_resource FOREIGN KEY (id_acl_resource) REFERENCES acl_resource (parent_id_acl_resource); ";
			$this->dataSource->query($sql);

			$sql = "ALTER TABLE acl_privilegies " .
				"ADD CONSTRAINT fk_acl_privilegies1 FOREIGN KEY (id_acl_role) REFERENCES acl_role (id_acl_role), " .
				"ADD CONSTRAINT fk_acl_privilegies2 FOREIGN KEY (id_acl_resource) REFERENCES acl_resource (id_acl_resource); ";
			$this->dataSource->query($sql);

		}catch(\dayscript\exception\Exception $e){
			throw $e;
			return false;
		}
			
		return true;
	}

	/**
	 * @see \dayscript\acl\persistence\AclPersistence
	 */
	public function commit(\dayscript\acl\Acl $acl){

		$this->acl = $acl;
		$roles = $this->acl->getRoles();
		
		foreach($roles as $rol){
			// Check if the role doesn't exists
			$instance = $rol['instance'];
			$sql = "SELECT * FROM acl_role WHERE id_acl_role = '". $instance->getRoleId() ."'";
			$tmp = $this->dataSource->fetchOne($sql);

			if(empty($tmp)){
				$sql = "INSERT INTO acl_role VALUES ('". $instance->getRoleId() ."') ";
				$this->dataSource->query($sql);

				$parents = $rol['parents'];
				foreach($parents as $parent){
					$sql = "SELECT * FROM acl_role WHERE id_acl_role = '". $parent->getRoleId() ."'";
					$tmp = $this->dataSource->fetchOne($sql);

					if(empty($tmp)){
						$sql = "INSERT INTO acl_role VALUES ('". $instance->getRoleId() ."') ";
						$this->dataSource->query($sql);
					}

					$sql = "SELECT * FROM acl_role_parents WHERE id_acl_role = '". $instance->getRoleId() ."' AND parent_id_acl_role = '". $parent->getRoleId() ."'";
					$tmp = $this->dataSource->fetchOne($sql);

					if(empty($tmp)){
						$sql = "INSERT INTO acl_role_parents VALUES ('". $instance->getRoleId() ."', '". $parent->getRoleId() ."') ";
						$this->dataSource->query($sql);
					}
				}
			}
		}

		$resources = $this->acl->getResources();
		foreach($resources as $resource){
			// Check if the role doesn't exists
			$instance = $resource['instance'];
			$parent = $resource['parent'];
			$sql = "SELECT * FROM acl_resource WHERE id_acl_resource = '". $instance->getResourceId() ."'";
			$tmp = $this->dataSource->fetchOne($sql);

			if(empty($tmp)){
				if(isset($parent)){
					$sql = "SELECT * FROM acl_resource WHERE id_acl_resource = '". $parent->getResourceId() ."'";
					$tmp = $this->dataSource->fetchOne($sql);

					if(empty($tmp)){
						$sql = "INSERT INTO acl_resource VALUES ('". $parent->getResourceId() ."') ";
						$this->dataSource->query($sql);
					}

					$sql = "INSERT INTO acl_resource VALUES ('". $instance->getResourceId() ."', '". $parent->getResourceId() ."') ";
					$this->dataSource->query($sql);
				}else{
					$sql = "INSERT INTO acl_resource VALUES ('". $instance->getResourceId() ."', NULL) ";
					$this->dataSource->query($sql);
				}
			}
		}

		$rules = $this->acl->getRules();

		$resources = $rules['byResourceId'];
		$resourcesKeys = \array_keys($resources);
		$n = \count($resources);

		for($i = 0; $i < $n; $i++){
			$resource = $resourcesKeys[$i];

			$roles = $resources[$resource]['byRoleId'];
			$rolesKeys = \array_keys($roles);
			$m = \count($roles);

			for($j = 0; $j < $m; $j++){
				$role = $rolesKeys[$j];

				$privilegies = $roles[$role]['byPrivilegeId'];
				$privilegiesKeys = \array_keys($privilegies);
				$l = \count($privilegies);

				for($k = 0; $k < $l; $k++){
					$privilegie = $privilegiesKeys[$k];

					$sql = "SELECT * FROM acl_privilegies " .
							"WHERE id_acl_role = '". $role ."' " .
							"AND id_acl_resource = '" .$resource. "' " . 
							"AND privilegie = '".$privilegie."'";
					$tmp = $this->dataSource->fetchOne($sql);

					if(empty($tmp)){
						$sql = "INSERT INTO acl_privilegies " .
								"VALUES ('". $role ."', '". $resource ."', '". $privilegie ."') ";
						$this->dataSource->query($sql);
					}
				}

				if(\array_key_exists('allPrivileges', $roles[$role])){
					$sql = "SELECT * FROM acl_privilegies " .
							"WHERE id_acl_role = '". $role ."' " .
							"AND id_acl_resource = '" .$resource. "' " . 
							"AND privilegie = '*'";
					$tmp = $this->dataSource->fetchOne($sql);

					if(empty($tmp)){
						$sql = "INSERT INTO acl_privilegies " .
								"VALUES ('". $role ."', '". $resource ."', '*') ";
						$this->dataSource->query($sql);
					}
				}
			}
		}
	}

	/**
	 * @see \dayscript\acl\persistence\AclPersistence
	 */
	public function load($rol = null){

		$this->acl = Acl::getInstance();
		
		$isRol = isset($rol);
		$isArray = \is_array($rol);
		
		// roles
		$sql = "SELECT * FROM acl_role ";
		if($isRol){
			$sql .= "WHERE id_acl_role ";
			if(!$isArray){
				$sql .= "= '". $rol->getRoleId() ."' ";
			}else{
				$sql .= "IN (";
				foreach($rol as $role){
					$sql .= "'". $role->getRoleId() . "',";
				}
				$sql = \substr($sql, 0, \strlen($sql) - 2) . ") ";
			}
		}	
		
		$roles = $this->dataSource->fetchAll($sql);

		foreach($roles as $r){
			$rol = new \dayscript\acl\role\ConcreteRole($r['id_acl_role']);
				
			self::loadRol($rol);
		}

		// resources
		$sql = "SELECT r.* FROM acl_resource r ";
		if($isRol){
			$sql .= ", acl_privilegies p " . 
					"WHERE r.id_acl_resource = p.id_acl_resource " . 
					"AND p.id_acl_role ";
			if(!$isArray){
				$sql .= "= '". $rol->getRoleId() ."' ";
			}else{
				$sql .= "IN (";
				foreach($rol as $role){
					$sql .= "'". $role->getRoleId() . "',";
				}
				$sql = \substr($sql, 0, \strlen($sql) - 2) . ") ";
			}
		}
		$resources = $this->dataSource->fetchAll($sql);

		foreach($resources as $r){
			self::loadResource($r);
		}

		// privilegies
		$sql = "SELECT * FROM acl_privilegies ";
		if($isRol){
			$sql .= "WHERE id_acl_role ";
			if(!$isArray){
				$sql .= "= '". $rol->getRoleId() ."' ";
			}else{
				$sql .= "IN (";
				foreach($rol as $role){
					$slq .= "'". $role->getRoleId() . "',";
				}
				$sql = \substr($sql, 0, \strlen($sql) - 2) . ") ";
			}
		}
		
		$privilegies = $this->dataSource->fetchAll($sql);

		foreach($privilegies as $p){
			$rol = new \dayscript\acl\role\ConcreteRole($p['id_acl_role']);
			$resource = new \dayscript\acl\resource\ConcreteResource($p['id_acl_resource']);

			if($p['privilegie'] == "*"){
				$this->acl->allow($rol, $resource);
			}else{
				$this->acl->allow($rol, $resource, $p['privilegie']);
			}
		}
		
		return $this->acl;

	}

	/**
	 * Load recursively a role and it's parents
	 *
	 * @param $rol
	 * @return void
	 */
	private function loadRol(\dayscript\acl\role\Role $rol){
		$sql = "SELECT * FROM acl_role_parents WHERE id_acl_role = '". $rol->getRoleId() ."' ";
		$parents = $this->dataSource->fetchAll($sql);

		if(!empty($parents)){
			$n = \count($parents);
			for($i = 0; $i < $n; $i++){
				$parent = new \dayscript\acl\role\ConcreteRole($parents[$i]['parent_id_acl_role']);
				$parents[$i] = $parent;

				self::loadRol($parent);

				if(!$this->acl->hasRole($parent)){
					$this->acl->addRole($parent);
				}
			}

			if(!$this->acl->hasRole($rol)){
				$this->acl->addRole($rol, $parents);
			}
		}else{
			if(!$this->acl->hasRole($rol)){
				$this->acl->addRole($rol);
			}
		}
	}

	/**
	 * Load recursively the resource and it's parents
	 * 
	 * @param $array
	 * @return void
	 */
	private function loadResource($array){
		$resource = new \dayscript\acl\resource\ConcreteResource($array['id_acl_resource']);
		if(isset($array['parent_id_acl_resource'])){
			$parent = new \dayscript\acl\resource\ConcreteResource($array['parent_id_acl_resource']);

			$sql = "SELECT * FROM acl_resource WHERE id_acl_resource = '". $array["parent_id_acl_resource"] ."'";
			$tmp = $this->dataSource->fetchAll($sql);
			
			self::loadResource($tmp[0]);

			if(!$this->acl->has($resource)){
				$this->acl->add($resource, $parent);
			}
		}else{
			if(!$this->acl->has($resource)){
				$this->acl->add($resource);
			}
		}
	}
}